package edu.hawaii.myisern.model;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import edu.hawaii.myisern.collaborations.jaxb.CollaboratingOrganizations;
import edu.hawaii.myisern.collaborations.jaxb.Collaboration;
import edu.hawaii.myisern.collaborations.jaxb.CollaborationTypes;
import edu.hawaii.myisern.collaborations.jaxb.Collaborations;
import edu.hawaii.myisern.collaborations.jaxb.OutcomeTypes;
import edu.hawaii.myisern.collaborations.jaxb.Years;
import edu.hawaii.myisern.organizations.jaxb.AffiliatedResearchers;
import edu.hawaii.myisern.organizations.jaxb.Organization;
import edu.hawaii.myisern.organizations.jaxb.Organizations;
import edu.hawaii.myisern.organizations.jaxb.ResearchKeywords;
import edu.hawaii.myisern.researchers.jaxb.Researcher;
import edu.hawaii.myisern.researchers.jaxb.Researchers;

/** 
 * Provides a persistent store for our myisern using embedded Derby.
 * Not intended as an example of good database design.
 * Instead, just shows how to get embed a DB backend within the Model with minimal example.  
 * 
 * Note: In embedded mode, connections are "free", so we make no effort to manage them via pooling.
 * 
 * @author Philip Johnson
 * @author Ed Kim
 * @author Jon Lao
 * @author JianFei Liao
 * @author Jared Sunouchi
 */
public class DerbyPersistentStore {

  /** The JDBC driver. */
  //private static final String driver = "org.apache.derby.jdbc.EmbeddedDriver";
  private static final String driver = "org.apache.derby.jdbc.ClientDriver";
  
  /**  The Derby connection URL. */ 
  //private static final String connectionURL = "jdbc:derby:myiserndb;create=true";
  private static final String connectionURL = "jdbc:derby://localhost:1527/myisern;create=true";
  
  /** The key for putting/retrieving the directory where Derby will create its databases. */
  private static final String derbySystemKey = "derby.system.home";
  
  /** Tables names */
  private static final String researcherTable = "researchers";
  private static final String organizationTable = "organizations";
  private static final String collaborationTable = "collaborations";
  
  /** Create table statements */
  private static final String createResearchers =
    "CREATE TABLE " + researcherTable + "(" +
    "researcher_id VARCHAR(255) PRIMARY KEY, " +
    "name VARCHAR(255), " + 
    "organization VARCHAR(255), " +
    "email VARCHAR(255), " +
    "pictureLink VARCHAR(255), " +
    "bioStatement VARCHAR(1000)" +
    ")";
  private static final String createOrganizations =
    "CREATE TABLE " + organizationTable + "(" +
    "organization_id VARCHAR(255) PRIMARY KEY, " + 
    "name VARCHAR(255), " +
    "type VARCHAR(255), " + 
    "contact VARCHAR(255), " +
    "affiliatedResearchers VARCHAR(500), " + 
    "country VARCHAR(255), " +
    "researchKeywords VARCHAR(500), " +
    "researchDescription VARCHAR(1000), " +
    "homePage VARCHAR(255)" +
    ")";
  private static final String createCollaborations =
    "CREATE TABLE " + collaborationTable + "(" +
    "collaboration_id VARCHAR(255) PRIMARY KEY, " + 
    "name VARCHAR(255), " +
    "collaboratingOrganizations VARCHAR(500), " + 
    "collaborationTypes VARCHAR(500), " +
    "years VARCHAR(255), " + 
    "outcomeTypes VARCHAR(500), " +
    "description VARCHAR(1000)" +
    ")";
  
  /** Insert statements */
  private static final String insertResearcher = 
    "INSERT INTO " + researcherTable + " (" +
    "researcher_id, " + 
    "name, " +
    "organization, " +
    "email, " +
    "pictureLink, " + 
    "bioStatement" +
    ") VALUES (?, ?, ?, ?, ?, ?)";
  private static final String insertOrganization = 
    "INSERT INTO " + organizationTable + " (" +
    "organization_id, " + 
    "name, " +
    "type, " + 
    "contact, " +
    "affiliatedResearchers, " + 
    "country, " +
    "researchKeywords, " +
    "researchDescription, " +
    "homePage" +
    ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
  private static final String insertCollaboration = 
    "INSERT INTO " + collaborationTable + " (" +
    "collaboration_id, " + 
    "name, " +
    "collaboratingOrganizations, " + 
    "collaborationTypes, " +
    "years, " + 
    "outcomeTypes, " +
    "description" +
    ") VALUES (?, ?, ?, ?, ?, ?, ?)";
  
  /** Update statements */
  private static final String updateResearcher = 
    "UPDATE " + researcherTable + " SET " +
    "organization = ?, " +
    "email = ?, " +
    "pictureLink = ?, " + 
    "bioStatement = ? " +
    "WHERE researcher_id = ?";
  private static final String updateOrganization = 
    "UPDATE " + organizationTable + " SET " +
    "type = ?, " +
    "contact = ?, " +
    "affiliatedResearchers = ?, " +
    "country = ?, " +
    "researchKeywords = ?, " +
    "researchDescription = ?, " +
    "homePage = ? " +
    "WHERE organization_id = ?";
  private static final String updateCollaboration = 
    "UPDATE " + collaborationTable + " SET " +
    "collaboratingOrganizations = ?, " + 
    "collaborationTypes = ?, " +
    "years = ?, " + 
    "outcomeTypes = ?, " +
    "description = ? " +
    "WHERE collaboration_id = ?";
  
  /** Delete statements */
  private static final String removeResearcher = 
    "DELETE FROM " + researcherTable + " WHERE " +
    "researcher_id = ?";
  private static final String removeOrganization = 
    "DELETE FROM " + organizationTable + " WHERE " +
    "organization_id = ?";
  private static final String removeCollaboration = 
    "DELETE FROM " + collaborationTable + " WHERE " +
    "collaboration_id = ?";
  
  /** A converter between list and objects. */
  private ListStringConverter converter = new ListStringConverter();
  
  
  /**
   * Creates the persistent store for the myisern in ~/.myiserndb/db. 
   */
  public DerbyPersistentStore () {
    // Make sure the directory exists, creating it if not present.
    File myisernHome = new File(System.getProperty("user.home"), ".myiserndb");
    File dbHome = new File(myisernHome, "db");
    dbHome.mkdirs();
    // Set the system property used by Derby to locate the db files. 
    System.getProperties().put(derbySystemKey, dbHome.getAbsolutePath());
    // Now load the Derby driver. 
    try {
      Class.forName(driver); 
    } 
    catch (ClassNotFoundException e) {
      String msg = "Exception during DerbyPersistentStore initialization: Derby not on CLASSPATH.";
      throw new RuntimeException(msg, e);
    }
    
  }
  
  /**
   * Builds and returns a new Researchers instance by reading values from the Derby DB.
   * If the DB cannot be read, then an empty Researchers instance is returned.
   * 
   * @return The researchers instance retrieved from the database (empty if not found).
   */
  Researchers getResearchers() {
    Researchers rCollection = new Researchers();
    Connection conn = null;
    Statement statement = null;
    ResultSet results = null;
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      results = statement.executeQuery("SELECT * FROM " + researcherTable + " ORDER BY name");
      while (results.next()) {
        Researcher newResearcher = new Researcher();
        newResearcher.setName(results.getString("name"));
        newResearcher.setOrganization(results.getString("organization"));
        newResearcher.setEmail(results.getString("email"));
        newResearcher.setPictureLink(results.getString("pictureLink"));
        newResearcher.setBioStatement(results.getString("bioStatement"));
        rCollection.getResearcher().add(newResearcher);
      }
    }
    catch (SQLException e) { 
      e.getMessage();
      // return null to indicate the table is not available
      return null;
    }
    finally {
      try {
        results.close();
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // out of options.
      }
    }
    return rCollection;
  }
  
  /**
   * Builds and returns a new Organizations instance by reading values from the Derby DB.
   * If the DB cannot be read, then an empty Organizations instance is returned.
   * 
   * @return The organizations instance retrieved from the database (empty if not found).
   */
  Organizations getOrganizations() {
    Organizations oCollection = new Organizations();
    Connection conn = null;
    Statement statement = null;
    ResultSet results = null;
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      results = statement.executeQuery("SELECT * FROM " + organizationTable + " ORDER BY name");
      while (results.next()) {
        Organization newOrganization = new Organization();
        newOrganization.setName(results.getString("name"));
        newOrganization.setType(results.getString("type"));
        newOrganization.setContact(results.getString("contact"));
        AffiliatedResearchers orgAF = new AffiliatedResearchers();
        orgAF.getAffiliatedResearcher().addAll(this.converter.stringToList(results
            .getString("affiliatedResearchers")));
        newOrganization.setAffiliatedResearchers(orgAF);
        newOrganization.setCountry(results.getString("country"));
        ResearchKeywords orgRK = new ResearchKeywords();
        orgRK.getResearchKeyword()
            .addAll(this.converter.stringToList(results.getString("researchKeywords")));
        newOrganization.setResearchKeywords(orgRK);
        newOrganization.setResearchDescription(results.getString("researchDescription"));
        newOrganization.setHomePage(results.getString("homePage"));
        oCollection.getOrganization().add(newOrganization);
      }
    }
    catch (SQLException e) { 
      e.getMessage();
      // return null to indicate the table is not available
      return null;
    }
    finally {
      try {
        results.close();
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // out of options.
      }
    }
    return oCollection;
  }
  
  /**
   * Builds and returns a new Organizations instance by reading values from the Derby DB.
   * If the DB cannot be read, then an empty Organizations instance is returned.
   * 
   * @return The organizations instance retrieved from the database (empty if not found).
   */
  Collaborations getCollaborations() {
    Collaborations cCollection = new Collaborations();
    Connection conn = null;
    Statement statement = null;
    ResultSet results = null;
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      results = statement.executeQuery("SELECT * FROM " + collaborationTable + " ORDER BY name");
      while (results.next()) {
        Collaboration newCollaboration = new Collaboration();
        newCollaboration.setName(results.getString("name"));
        CollaboratingOrganizations collabCO = new CollaboratingOrganizations();
        collabCO.getCollaboratingOrganization().addAll(this.converter.stringToList(results
            .getString("collaboratingOrganizations")));
        newCollaboration.setCollaboratingOrganizations(collabCO);
        CollaborationTypes collabCT = new CollaborationTypes();
        collabCT.getCollaborationType().addAll(this.converter.stringToList(results
            .getString("collaborationTypes")));
        newCollaboration.setCollaborationTypes(collabCT);
        Years collabY = new Years();
        collabY.getYear()
            .addAll(this.converter.stringToBigIntegerList(results.getString("years")));
        newCollaboration.setYears(collabY);
        OutcomeTypes collabOT = new OutcomeTypes();
        collabOT.getOutcomeType()
            .addAll(this.converter.stringToList(results.getString("outcomeTypes")));
        newCollaboration.setOutcomeTypes(collabOT);
        newCollaboration.setDescription(results.getString("description"));
        cCollection.getCollaboration().add(newCollaboration);
      }
    }
    catch (SQLException e) { 
      e.getMessage();
      // return null to indicate the table is not available
      return null;
    }
    finally {
      try {
        results.close();
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // out of options.
      }
    }
    return cCollection;
  }
  
  /**
   * Persists the researchers.
   *  
   * @param rCollection The researchers to be persisted.
   */
  void writeResearchers(Researchers rCollection) {
    List<Researcher> researcherList = rCollection.getResearcher();
    Connection conn = null;
    PreparedStatement statement = null;
    try {
      createEmptyResearchersTable();
      conn = DriverManager.getConnection(connectionURL);
      for (Researcher res : researcherList) {
        statement = conn.prepareStatement(insertResearcher);
        statement.setString(1, res.getName().replace(' ', '_'));
        statement.setString(2, res.getName());
        statement.setString(3, res.getOrganization());
        statement.setString(4, res.getEmail());
        statement.setString(5, res.getPictureLink());
        statement.setString(6, res.getBioStatement());
        statement.executeUpdate();
      }
    }
    catch (SQLException e) { //NOPMD
      // do nothing
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
  }
  
  /**
   * Persists the organizations.
   *  
   * @param oCollection The organizations to be persisted.
   */
  void writeOrganizations(Organizations oCollection) {
    List<Organization> organizationList = oCollection.getOrganization();
    Connection conn = null;
    PreparedStatement statement = null;
    try {
      createEmptyOrganizationsTable();
      conn = DriverManager.getConnection(connectionURL);
      for (Organization org : organizationList) {
        statement = conn.prepareStatement(insertOrganization);
        statement.setString(1, org.getName().replace(' ', '_'));
        statement.setString(2, org.getName());
        statement.setString(3, org.getType());
        statement.setString(4, org.getContact());
        statement.setString(5, this.converter.listToString(org.getAffiliatedResearchers()
            .getAffiliatedResearcher()));
        statement.setString(6, org.getCountry());
        statement.setString(7, 
            this.converter.listToString(org.getResearchKeywords().getResearchKeyword()));
        statement.setString(8, org.getResearchDescription());
        statement.setString(9, org.getHomePage());
        statement.executeUpdate();
      }
    }
    catch (SQLException e) { //NOPMD
      // do nothing
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
  }
  
  /**
   * Persists the collaborations.
   *  
   * @param cCollection The collaborations to be persisted.
   */
  void writeCollaborations(Collaborations cCollection) {
    List<Collaboration> collaborationList = cCollection.getCollaboration();
    Connection conn = null;
    PreparedStatement statement = null;
    try {
      createEmptyCollaborationsTable();
      conn = DriverManager.getConnection(connectionURL);
      for (Collaboration collab : collaborationList) {
        statement = conn.prepareStatement(insertCollaboration);
        statement.setString(1, collab.getName().replace(' ', '_'));
        statement.setString(2, collab.getName());
        statement.setString(3, this.converter.listToString(collab.getCollaboratingOrganizations()
            .getCollaboratingOrganization()));
        statement.setString(4, this.converter.listToString(collab.getCollaborationTypes()
            .getCollaborationType()));
        statement.setString(5, this.converter.bigIntegerListToString(collab.getYears().getYear()));
        statement.setString(6, 
            this.converter.listToString(collab.getOutcomeTypes().getOutcomeType()));
        statement.setString(7, collab.getDescription());
        statement.executeUpdate();
      }
    }
    catch (SQLException e) { //NOPMD
      // do nothing
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
  }
  
  /**
   * Creates researchers data table in database, after deleting any pre-existing one.
   * 
   * @throws SQLException if there is a problem deleting or creating the table.
   */
  private void createEmptyResearchersTable() throws SQLException {
    Connection conn = null;
    Statement statement = null;

    // Start by deleting any existing table.  There may not be any.
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.execute("DROP TABLE " + researcherTable);
    }
    catch (SQLException e) { //NOPMD
      // Ignore table drop errors since table may not exist when we try to delete it.
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
    
    // Now that any prior table has been deleted, create a new one.
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.execute(createResearchers);
    }
    catch (SQLException e) { //NOPMD
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
  }
  
  /**
   * Creates organizations data table in database, after deleting any pre-existing one.
   * 
   * @throws SQLException if there is a problem deleting or creating the table.
   */
  private void createEmptyOrganizationsTable() throws SQLException {
    Connection conn = null;
    Statement statement = null;

    // Start by deleting any existing table.  There may not be any.
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.execute("DROP TABLE " + organizationTable);
    }
    catch (SQLException e) { //NOPMD
      // Ignore table drop errors since table may not exist when we try to delete it.
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
    
    // Now that any prior table has been deleted, create a new one.
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.execute(createOrganizations);
    }
    catch (SQLException e) { //NOPMD
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
  }
  
  /**
   * Creates collaborations data table in database, after deleting any pre-existing one.
   * 
   * @throws SQLException if there is a problem deleting or creating the table.
   */
  private void createEmptyCollaborationsTable() throws SQLException {
    Connection conn = null;
    Statement statement = null;

    // Start by deleting any existing table.  There may not be any.
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.execute("DROP TABLE " + collaborationTable);
    }
    catch (SQLException e) { //NOPMD
      // Ignore table drop errors since table may not exist when we try to delete it.
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
    
    // Now that any prior table has been deleted, create a new one.
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.createStatement();
      statement.execute(createCollaborations);
    }
    catch (SQLException e) { //NOPMD
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
  }
  
  /**
   * Inserts the researcher.
   * 
   * @param res The researcher to be inserted.
   */
  void insertResearcher(Researcher res) {
    Connection conn = null;
    PreparedStatement statement = null;
    
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.prepareStatement(insertResearcher);
      statement.setString(1, res.getName().replace(' ', '_'));
      statement.setString(2, res.getName());
      statement.setString(3, res.getOrganization());
      statement.setString(4, res.getEmail());
      statement.setString(5, res.getPictureLink());
      statement.setString(6, res.getBioStatement());
      statement.executeUpdate();
    }
    catch (SQLException e) { //NOPMD
      System.out.println("Insert Researcher SQL Error.");
      System.out.println("Error Message: " + e.getMessage());
      e.printStackTrace();
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
  }
  
  /**
   * Updates the researcher.
   * 
   * @param res The researcher to be updated.
   */
  void updateResearcher(Researcher res) {
    Connection conn = null;
    PreparedStatement statement = null;
    
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.prepareStatement(updateResearcher);
      statement.setString(1, res.getOrganization());
      statement.setString(2, res.getEmail());
      statement.setString(3, res.getPictureLink());
      statement.setString(4, res.getBioStatement());
      statement.setString(5, res.getName().replace(' ', '_'));
      statement.executeUpdate();
    }
    catch (SQLException e) { //NOPMD
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
  }
  
  /**
   * Removes the researcher.
   * 
   * @param res The researcher to be removed.
   */
  void removeResearcher(Researcher res) {
    Connection conn = null;
    PreparedStatement statement = null;
    
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.prepareStatement(removeResearcher);
      statement.setString(1, res.getName().replace(' ', '_'));
      statement.executeUpdate();
    }
    catch (SQLException e) { //NOPMD
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
  }
  
  /**
   * Inserts the organization.
   * 
   * @param org The organization to be inserted.
   */
  void insertOrganization(Organization org) {
    Connection conn = null;
    PreparedStatement statement = null;
    
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.prepareStatement(insertOrganization);
      statement.setString(1, org.getName().replace(' ', '_'));
      statement.setString(2, org.getName());
      statement.setString(3, org.getType());
      statement.setString(4, org.getContact());
      statement.setString(5, this.converter.listToString(org.getAffiliatedResearchers()
          .getAffiliatedResearcher()));
      statement.setString(6, org.getCountry());
      statement.setString(7, 
          this.converter.listToString(org.getResearchKeywords().getResearchKeyword()));
      statement.setString(8, org.getResearchDescription());
      statement.setString(9, org.getHomePage());
      statement.executeUpdate();
    }
    catch (SQLException e) { //NOPMD
      System.out.println("Insert Organization SQL Error.");
      System.out.println("Error Message: " + e.getMessage());
      e.printStackTrace();
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
  }
  
  /**
   * Updates the organization.
   * 
   * @param org The organization to be updated.
   */
  void updateOrganization(Organization org) {
    Connection conn = null;
    PreparedStatement statement = null;
    
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.prepareStatement(updateOrganization);
      statement.setString(1, org.getType());
      statement.setString(2, org.getContact());
      statement.setString(3, this.converter.listToString(org.getAffiliatedResearchers()
          .getAffiliatedResearcher()));
      statement.setString(4, org.getCountry());
      statement.setString(5, this.converter.listToString(org.getResearchKeywords()
          .getResearchKeyword()));
      statement.setString(6, org.getResearchDescription());
      statement.setString(7, org.getHomePage());
      statement.setString(8, org.getName().replace(' ', '_'));
      statement.executeUpdate();
    }
    catch (SQLException e) { //NOPMD
      System.out.println("SQL Exception Thrown.");
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
  }
  
  /**
   * Removes the organization.
   * 
   * @param org The organization to be removed.
   */
  void removeOrganization(Organization org) {
    Connection conn = null;
    PreparedStatement statement = null;
    
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.prepareStatement(removeOrganization);
      statement.setString(1, org.getName().replace(' ', '_'));
      statement.executeUpdate();
    }
    catch (SQLException e) { //NOPMD
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
  }
  
  /**
   * Inserts the collaboration.
   * 
   * @param collab The collaboration to be inserted.
   */
  void insertCollaboration(Collaboration collab) {
    Connection conn = null;
    PreparedStatement statement = null;
    
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.prepareStatement(insertCollaboration);
      statement.setString(1, collab.getName().replace(' ', '_'));
      statement.setString(2, collab.getName());
      statement.setString(3, this.converter.listToString(collab.getCollaboratingOrganizations()
          .getCollaboratingOrganization()));
      statement.setString(4, this.converter.listToString(collab.getCollaborationTypes()
          .getCollaborationType()));
      statement.setString(5, this.converter.bigIntegerListToString(collab.getYears().getYear()));
      statement.setString(6, 
          this.converter.listToString(collab.getOutcomeTypes().getOutcomeType()));
      statement.setString(7, collab.getDescription());
      statement.executeUpdate();
    }
    catch (SQLException e) { //NOPMD
      System.out.println("Insert Collaboration SQL Error.");
      System.out.println("Error Message: " + e.getMessage());
      e.printStackTrace();
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
  }
  
  /**
   * Updates the collaboration.
   * 
   * @param collab The collaboration to be updated.
   */
  void updateCollaboration(Collaboration collab) {
    Connection conn = null;
    PreparedStatement statement = null;
    
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.prepareStatement(updateCollaboration);
      statement.setString(1, this.converter.listToString(collab.getCollaboratingOrganizations()
          .getCollaboratingOrganization()));
      statement.setString(2, this.converter.listToString(collab.getCollaborationTypes()
          .getCollaborationType()));
      statement.setString(3, this.converter.bigIntegerListToString(collab.getYears().getYear()));
      statement.setString(4, 
          this.converter.listToString(collab.getOutcomeTypes().getOutcomeType()));
      statement.setString(5, collab.getDescription());
      statement.setString(6, collab.getName().replace(' ', '_'));
      statement.executeUpdate();
    }
    catch (SQLException e) { //NOPMD
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
  }
  
  /**
   * Removes the collaboration.
   * 
   * @param collab The collaboration to be removed.
   */
  void removeCollaboration(Collaboration collab) {
    Connection conn = null;
    PreparedStatement statement = null;
    
    try {
      conn = DriverManager.getConnection(connectionURL);
      statement = conn.prepareStatement(removeCollaboration);
      statement.setString(1, collab.getName().replace(' ', '_'));
      statement.executeUpdate();
    }
    catch (SQLException e) { //NOPMD
    }
    finally {
      try {
        statement.close();
        conn.close();
      }
      catch (Exception e) { //NOPMD
        // Out of options.
      }
    }
  }
  

  /*
  public boolean shutDown() {
    // a SQLException is always thrown when the database shuts down.
    boolean isShutDown = false;
    Connection conn = null;
    try {
      conn = DriverManager.getConnection("jdbc:derby:;shutdown=true");
    }
    catch (SQLException e) {
      isShutDown = true;
    }
    finally {
      try {
        // this would throw a null pointer exception because if the database shuts down
        // normally, SQL exception will be thrown and no value will be assigned to this connection
        conn.close();
      }
      catch (Exception e) {
        e.printStackTrace();
      }
    }
    return isShutDown;
  }*/
}